package com.example.pdfandword.excel;

import java.io.*;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.example.pdfandword.util.OssBootUtil;
import com.example.pdfandword.util.PdfConvertUtil;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ChangeExcelData {

    public static void main(String[] args) throws IOException, EncryptedDocumentException, InvalidFormatException {
        String path = "c://excel.xlsx";
        String outPath = "C://work/util/springboot+pdf+word/";

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("rzbj", "111111");
        params.put("xm", "姓名");
        params.put("htbh", "2002000001");
        params.put("htbh2", "2002000001");
        params.put("htbh3", "2002000001");

        new ChangeExcelData().replaceExcel(path, outPath, "111", "excel1.xlsx", params, "", "");
    }

    public String replaceExcelandprint(String inPath, String outPath, String orderNo, String filename, Map params, String contractType, String uploadType) throws IOException, InvalidFormatException {


        String filedoutpath = outPath + orderNo + "/out/";
        File fileout = new File(filedoutpath);
        if (!fileout.exists()) {
            fileout.mkdirs();// 创建文件根目录
        }

        InputStream is = new FileInputStream(new File(inPath));
        Workbook wb = WorkbookFactory.create(is);

        Sheet sheet = wb.getSheetAt(0);//获取Excel的工作表sheet，下标从0开始。
        int trLength = sheet.getLastRowNum();//获取Excel的行数
        for (int i = 0; i < trLength; i++) {
            Row row = sheet.getRow(i);//获取Excel的行，下标从0开始
            if (row == null) {//若行为空，则遍历下一行
                continue;
            }
            int minColIx = row.getFirstCellNum();
            int maxColIx = row.getLastCellNum();
            for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                Cell cell = row.getCell(colIx);//获取指定单元格，单元格从左到右下标从0开始
                String runText = "";
                if (cell.getCellType() == 1) {
                    runText = cell.getStringCellValue();
                }

                if (runText.equals("")) {
                    continue;
                }
                System.out.println(cell);
                Matcher matcher = this.matcher(runText);
                if (matcher.find()) {
                    while ((matcher = this.matcher(runText)).find()) {
                        runText = matcher.replaceFirst(String.valueOf(params.get(matcher.group(1))));
                    }
                    cell.setCellValue(runText);
                }
            }
        }
        OutputStream out = new FileOutputStream(new File(filedoutpath + filename));
        wb.write(out);
        is.close();
        out.close();
        PdfConvertUtil.printexcel(filedoutpath + filename);

        return "success";

    }

    public String replaceExcel(String inPath, String outPath, String orderNo, String filename, Map params, String contractType, String uploadType) throws IOException, InvalidFormatException {


        String filedoutpath = outPath + orderNo + "/out/";
        File fileout = new File(filedoutpath);
        if (!fileout.exists()) {
            fileout.mkdirs();// 创建文件根目录
        }

        InputStream is = new FileInputStream(new File(inPath));
        Workbook wb = WorkbookFactory.create(is);

        Sheet sheet = wb.getSheetAt(0);//获取Excel的工作表sheet，下标从0开始。
        int trLength = sheet.getLastRowNum();//获取Excel的行数
        for (int i = 0; i < trLength; i++) {
            Row row = sheet.getRow(i);//获取Excel的行，下标从0开始
            if (row == null) {//若行为空，则遍历下一行
                continue;
            }
            int minColIx = row.getFirstCellNum();
            int maxColIx = row.getLastCellNum();
            for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                Cell cell = row.getCell(colIx);//获取指定单元格，单元格从左到右下标从0开始
                String runText = "";
                if (cell.getCellType() == 1) {
                    runText = cell.getStringCellValue();
                }

                if (runText.equals("")) {
                    continue;
                }
                System.out.println(cell);
                Matcher matcher = this.matcher(runText);
                if (matcher.find()) {
                    while ((matcher = this.matcher(runText)).find()) {
                        runText = matcher.replaceFirst(String.valueOf(params.get(matcher.group(1))));
                    }
                    cell.setCellValue(runText);
                }
            }
        }
        OutputStream out = new FileOutputStream(new File(filedoutpath + filename));
        wb.write(out);
        is.close();
        out.close();
        File file = new File(filedoutpath + filename);
        String fileurl = OssBootUtil.uploadlocalfile(file, "hetong", "");
        return fileurl;

    }

    public String replaceExcelpdf(String inPath, String outPath, String orderNo, String filename, Map params) throws IOException, InvalidFormatException {


        String filedoutpath = outPath + orderNo + "/out/";
        File fileout = new File(filedoutpath);
        if (!fileout.exists()) {
            fileout.mkdirs();// 创建文件根目录
        }

        InputStream is = new FileInputStream(new File(inPath));
        Workbook wb = WorkbookFactory.create(is);

        Sheet sheet = wb.getSheetAt(0);//获取Excel的工作表sheet，下标从0开始。
        int trLength = sheet.getLastRowNum();//获取Excel的行数
        for (int i = 0; i < trLength; i++) {
            Row row = sheet.getRow(i);//获取Excel的行，下标从0开始
            if (row == null) {//若行为空，则遍历下一行
                continue;
            }
            int minColIx = row.getFirstCellNum();
            int maxColIx = row.getLastCellNum();
            for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                Cell cell = row.getCell(colIx);//获取指定单元格，单元格从左到右下标从0开始
                String runText = "";
                if (cell.getCellType() == 1) {
                    runText = cell.getStringCellValue();
                }

                if (runText.equals("")) {
                    continue;
                }
                System.out.println(cell);
                Matcher matcher = this.matcher(runText);
                if (matcher.find()) {
                    while ((matcher = this.matcher(runText)).find()) {
                        runText = matcher.replaceFirst(String.valueOf(params.get(matcher.group(1))));
                    }
                    cell.setCellValue(runText);
                }
            }
        }
        OutputStream out = new FileOutputStream(new File(filedoutpath + filename));
        wb.write(out);
        is.close();
        out.close();
        File file = new File(filedoutpath + filename);
        String fileurl = OssBootUtil.uploadlocalfile(file, "hetong", "");
        return fileurl;

    }

    /**
     * 正则匹配字符串
     *
     * @param str
     * @return
     */
    private Matcher matcher(String str) {
        Pattern pattern = Pattern.compile("\\{(.+?)\\}", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(str);
        return matcher;
    }
}

